﻿using Ext.Net;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Reflection;

public partial class FeedBackReport : ExtBaseRpt
{
    mrjapp mrj = new mrjapp();
    string mid;
    protected void Page_Load(object sender, EventArgs e)
    {
        mid = Request.Params["mid"];
        if (!X.IsAjaxRequest)
        {
            try
            {
                InsActLog(mid, "浏览");

                string strSql = string.Format(@"SELECT DISTINCT OperatorAreaName FROM t_task_shop_permissions WHERE Display='{0}'", Session["UserName"].ToString());
                DataSet ds = mydb.DM_GetDateSet(strSql);
                if (ds != null && ds.Tables[0].Rows.Count > 0) //控制陈列师查看的数据权限
                {
                    this.txtOperatorAreaName.Text = ds.Tables[0].Rows[0]["OperatorAreaName"].ToString();
                    GetBigAreaName(null, null);
                    txtOperatorAreaName.GetStore().DataSource = mydb.DM_GetDateSet(strSql);
                    string sqlDisplay = string.Format(@"SELECT DISTINCT Display FROM t_task_shop_permissions WHERE ShopState<>'已撤店' AND Display IS NOT NULL AND OperatorAreaName IN ({0}) ", strSql);
                    txtDisplay.GetStore().DataSource = mydb.DM_GetDateSet(sqlDisplay);
                    string sqlShopManager = string.Format(@"SELECT DISTINCT ShopManager FROM t_task_shop_permissions WHERE ShopState<>'已撤店' AND ShopManager IS NOT NULL AND OperatorAreaName IN ({0}) ", strSql);
                    txtShopManager.GetStore().DataSource = mydb.DM_GetDateSet(sqlShopManager);
                }
                else
                {
                    txtOperatorAreaName.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT OperatorAreaName FROM t_task_shop_permissions ");
                    txtDisplay.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT Display FROM t_task_shop_permissions WHERE ShopState<>'已撤店' AND Display IS NOT NULL ");
                    txtShopManager.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT ShopManager FROM t_task_shop_permissions WHERE ShopState<>'已撤店' AND ShopManager IS NOT NULL ");
                }

                //时间默认当前月
                this.txtTimeBegin.Text = DateTime.Now.ToString("yyyy-MM-dd");
                this.txtTimeEnd.Text = DateTime.Now.ToString("yyyy-MM-dd");
                StartDateField(sender, null);

            }
            catch (Exception ex)
            {
                ShowBox("错误：" + ex.Message);
            }
        }
        if (Session["UserID"] == null)
        {
            ShowBox("用户登入超时请求重新登入");
        }
    }

    //获取省份
    protected void GetBigAreaName(object sender, DirectEventArgs e)
    {
        if (string.IsNullOrWhiteSpace(txtOperatorAreaName.Text))
        {
            txtBigAreaName.Text = string.Empty;
            txtBigAreaName.GetStore().RemoveAll();
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();
        }
        else
        {
            //先清空数据后再绑定
            txtBigAreaName.Text = string.Empty;
            txtBigAreaName.GetStore().RemoveAll();
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();

            string sql = string.Format(@"SELECT DISTINCT ProvinceName AS BigAreaName FROM t_task_shop_permissions WHERE OperatorAreaName='{0}' ", txtOperatorAreaName.Text);
            txtBigAreaName.GetStore().DataSource = mydb.DM_GetDateSet(sql);
            this.txtBigAreaName.DataBind();
        }
    }

    //获取城市
    protected void GetArea(object sender, DirectEventArgs e)
    {
        if (string.IsNullOrWhiteSpace(txtBigAreaName.Text))
        {
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();
        }
        else
        {
            //先清空数据后再绑定
            txtAreaName.Text = string.Empty;
            txtAreaName.GetStore().RemoveAll();

            txtAreaName.GetStore().DataSource = mydb.DM_GetDateSet("SELECT DISTINCT AreaName FROM t_task_shop_permissions WHERE ProvinceName='" + txtBigAreaName.Value + "' ");
            this.Store_AreaName.DataBind();
        }
    }
    
    protected void StartDateField(object sender, DirectEventArgs e)
    {
        this.txtTimeEnd.MinDate = Convert.ToDateTime(this.txtTimeBegin.Text);
        int Begin = Convert.ToInt32(Convert.ToDateTime(this.txtTimeBegin.Text).DayOfWeek);
        if (Begin == 0)
        {
            this.txtTimeEnd.MaxDate = Convert.ToDateTime(this.txtTimeBegin.Text);
            this.txtTimeEnd.Text = this.txtTimeBegin.Text;
        }
        else
        {
            this.txtTimeEnd.MaxDate = Convert.ToDateTime(this.txtTimeBegin.Text).AddDays(7 - Begin);
            if (Convert.ToDateTime(this.txtTimeEnd.Text) < Convert.ToDateTime(this.txtTimeBegin.Text))
            {
                this.txtTimeEnd.Text = this.txtTimeBegin.Text;
            }
            else if (Convert.ToDateTime(this.txtTimeEnd.Text) > Convert.ToDateTime(this.txtTimeBegin.Text).AddDays(7 - Begin))
            {
                this.txtTimeEnd.Text = Convert.ToDateTime(this.txtTimeBegin.Text).AddDays(7 - Begin).ToString();
            }
        }
    }


    protected void RefreshDataSet(object sender, StoreReadDataEventArgs e)
    {
        BtnQureyUser(null, null);
    }

    protected void BtnQureyUser(object sender, DirectEventArgs e)
    {
        try
        {
            this.Store1.DataSource = GetDataList();
            this.Store1.DataBind();
        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
            ShowBox("查询出错：" + ex.Message);
        }
    }

    public string SqlWhere()
    {
        string strWhere = string.Empty;
        var strOperationAreaName = this.txtOperatorAreaName.Text;
        var strBigAreaName = this.txtBigAreaName.Text;//省份
        var strAreaName = this.txtAreaName.Text;//市
        var strDisplay = this.txtDisplay.Text;
        var strManager = this.txtShopManager.Text;
        if (!string.IsNullOrEmpty(strOperationAreaName))
        {
            strWhere = strWhere + string.Format(@" AND a.OperatorAreaName='{0}' ", strOperationAreaName);
        }
        if (!string.IsNullOrEmpty(strBigAreaName))
        {
            strWhere = strWhere + string.Format(@" AND a.ProvinceName='{0}' ", strBigAreaName);
        }
        if (!string.IsNullOrEmpty(strAreaName))
        {
            strWhere = strWhere + string.Format(@" AND a.AreaName='{0}' ", strAreaName);
        }
        if (!string.IsNullOrEmpty(strDisplay))
        {
            strWhere = strWhere + string.Format(@" AND a.Display='{0}' ", strDisplay);
        }
        if (!string.IsNullOrEmpty(strManager))
        {
            strWhere = strWhere + string.Format(@" AND a.ShopManager='{0}' ", strManager);
        }
        return strWhere;
    }

    //获取列表数据
    public DataSet GetDataList()
    {
        string strWhere = SqlWhere();
        var txtTimeBegin = Convert.ToDateTime(this.txtTimeBegin.Text);
        var txtTimeEnd = Convert.ToDateTime(this.txtTimeEnd.Text);

        var sql = string.Format(@"SELECT 
	a.DetailID,
	'{0}' AS CurrentUser,
	'{1}～{2}' AS CheckCycle,
	a.OperatorAreaName,
	a.ProvinceName,
	a.AreaName,
	a.ShopCode,
	a.ShopName,
	a.Display,
	a.ShopManager,
	ts.TaskName,
	a.ProcessingTime,
	a.FeedBackDescribe,
	ISNULL(b.Url1, 0) AS Url1,
	ISNULL(b.Url2, 0) AS Url2,
    ISNULL(b.Url3, 0) AS Url3,
    ISNULL(b.Url4, 0) AS Url4,
    ISNULL(b.Url5, 0) AS Url5,
    ISNULL(b.Url6, 0) AS Url6,
    ISNULL(b.Url7, 0) AS Url7,
    ISNULL(b.Url8, 0) AS Url8,
    ISNULL(b.Url9, 0) AS Url9
FROM 
(
	SELECT tsp.OperatorAreaName,tsp.ProvinceName,tsp.AreaName,tsp.ShopName,c.ShopCode, tsp.Display,ISNULL(tsp.ShopManager,'') AS ShopManager,c.TaskID,c.DetailID,c.FeedBackDescribe,c.ProcessingTime FROM t_task_shop_permissions AS tsp
	INNER JOIN 
	(
		SELECT DISTINCT b.TaskID,b.DetailID,b.FeedBackDescribe,USDR.ShopCode,b.ProcessingTime FROM [V_UserShopDataRights] AS USDR
		INNER JOIN 
		(
			SELECT DISTINCT b.TaskID,a.ProcessingCreator,a.ProcessingDescribe,a.ProcessingTime,a.DetailID,a.FeedBackDescribe FROM t_task_shoplist AS b
			INNER JOIN 
			(
				SELECT a.TaskID,a.ProcessingCreator,a.ProcessingDescribe,MAX(a.ProcessingTime) AS ProcessingTime,MAX(a.DetailID) AS DetailID,f.FeedBackDescribe FROM t_task_detail_finish AS a
				INNER JOIN t_task_feedback AS f ON a.DetailID=f.DetailID
				WHERE 1=1 
				AND a.ProcessingTime>='{3}'  
				AND a.ProcessingTime<='{4}'
				--AND a.DetailID='132007'
				GROUP BY a.TaskID,a.ProcessingCreator,a.ProcessingDescribe,f.FeedBackDescribe
			) AS a ON a.TaskID=b.TaskID
		) AS b ON USDR.userid=b.ProcessingCreator
	) AS c ON tsp.ShopCode=c.shopcode AND tsp.ShopState<>'已撤店'
	GROUP BY tsp.OperatorAreaName,tsp.ProvinceName,tsp.AreaName,tsp.ShopName,c.ShopCode,tsp.Display,tsp.ShopManager,c.TaskID,c.DetailID,c.FeedBackDescribe,c.ProcessingTime
) AS a 
INNER JOIN t_task_set AS ts ON ts.TaskID=a.TaskID
LEFT JOIN 
(
	SELECT  
		f.DetailID,
		MAX(CASE WHEN f.rn = 1 THEN f.Url END) AS Url1,
		MAX(CASE WHEN f.rn = 2 THEN f.Url END) AS Url2,
		MAX(CASE WHEN f.rn = 3 THEN f.Url END) AS Url3,
		MAX(CASE WHEN f.rn = 4 THEN f.Url END) AS Url4,
		MAX(CASE WHEN f.rn = 5 THEN f.Url END) AS Url5, 
		MAX(CASE WHEN f.rn = 6 THEN f.Url END) AS Url6,
		MAX(CASE WHEN f.rn = 7 THEN f.Url END) AS Url7, 
		MAX(CASE WHEN f.rn = 8 THEN f.Url END) AS Url8, 
		MAX(CASE WHEN f.rn = 9 THEN f.Url END) AS Url9
	FROM 
	( 
		SELECT
			'http://kmer-online-pic.oss-cn-shenzhen.aliyuncs.com/' + ki.Url AS Url,
			tpi.DetailID ,
			ROW_NUMBER() OVER ( PARTITION BY DetailID ORDER BY ki.Url ) AS rn
		FROM dbo.t_task_problem_imglist AS tpi
		LEFT JOIN dbo.kmImages ki ON tpi.ImgID = ki.ID
	) f
	GROUP BY f.DetailID
) AS b ON a.DetailID=b.DetailID
WHERE 1=1
{5}
--AND a.OperatorAreaName='零售1部'
--AND a.ProvinceName='福建'
--AND a.AreaName='福州'
--AND a.Display='陈星宏'
--AND a.ShopManager='林祝年'
", Session["UserName"], txtTimeBegin.ToString("yyyy-MM-dd"),
            txtTimeEnd.ToString("MM-dd"), txtTimeBegin.ToString("yyyy-MM-dd"), txtTimeEnd.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss"), strWhere);

        return mydb.DM_GetDateSet(sql);


    }

    //导出EXCEL(未使用，有错)
    protected void Store_Submit(object sender, StoreSubmitDataEventArgs e)
    {
        ExtGridToExcel(mid, "xls", e.Xml, this.Response, "陈列反馈报告");
    }

    protected void BtnOutExcel(object sender, DirectEventArgs e)
    {
        //导出EXCEL
        try
        {
            DataSet ds = GetExportData();
            string path = NpoiHelper.ExportByWeb(mid, ds, "陈列反馈报告");
            this.ResourceManager1.AddScript("outExcle('" + path + "')");
            //if (ds == null || ds.Tables.Count <= 1)
            //    ShowBox("无数据！");
            //else
            //{
            //    //string path = NpoiHelper.ExportByWeb(mid, ds, this.Title);
            //    string path = NpoiHelper.ExportByWeb(mid, ds, "店铺巡检列表");
            //    this.ResourceManager1.AddScript("outExcle('" + path + "')");
            //}

        }
        catch (Exception ex)
        {
            Activity.Log.Error("请求报表数据异常：" + ex);
            ShowBox("导出出错：" + ex.Message);
        }
    }
    //获取导出的列表数据
    public DataSet GetExportData()
    {
        string strWhere = SqlWhere();
        var txtTimeBegin = Convert.ToDateTime(this.txtTimeBegin.Text);
        var txtTimeEnd = Convert.ToDateTime(this.txtTimeEnd.Text);

        var sql = string.Format(@"SELECT 
	'{0}' AS '检核人',
	'{1}～{2}' AS '检核周期',
	a.OperatorAreaName AS '分部',
	a.ProvinceName AS '省份',
	a.AreaName AS '城市',
	a.ShopCode AS '店铺代码',
	a.ShopName AS '店铺名称',
	a.Display AS '陈列师',
	a.ShopManager AS '店经理',
	ts.TaskName AS '问题区域',
	a.FeedBackDescribe AS '问题及跟踪事项',
	b.Url1 AS '图片1',
	b.Url2 AS '图片2',
    b.Url3 AS '图片3',
    b.Url4 AS '图片4',
    b.Url5 AS '图片5',
    b.Url6 AS '图片6',
    b.Url7 AS '图片7',
    b.Url8 AS '图片8',
    b.Url9 AS '图片9'
FROM 
(
	SELECT tsp.OperatorAreaName,tsp.ProvinceName,tsp.AreaName,tsp.ShopName,c.ShopCode, tsp.Display,ISNULL(tsp.ShopManager,'') AS ShopManager,c.TaskID,c.DetailID,c.FeedBackDescribe,c.ProcessingTime FROM t_task_shop_permissions AS tsp
	INNER JOIN 
	(
		SELECT DISTINCT b.TaskID,b.DetailID,b.FeedBackDescribe,USDR.ShopCode,b.ProcessingTime FROM [V_UserShopDataRights] AS USDR
		INNER JOIN 
		(
			SELECT DISTINCT b.TaskID,a.ProcessingCreator,a.ProcessingDescribe,a.ProcessingTime,a.DetailID,a.FeedBackDescribe FROM t_task_shoplist AS b
			INNER JOIN 
			(
				SELECT a.TaskID,a.ProcessingCreator,a.ProcessingDescribe,MAX(a.ProcessingTime) AS ProcessingTime,MAX(a.DetailID) AS DetailID,f.FeedBackDescribe FROM t_task_detail_finish AS a
				INNER JOIN t_task_feedback AS f ON a.DetailID=f.DetailID
				WHERE 1=1 
				AND a.ProcessingTime>='{3}'  
				AND a.ProcessingTime<='{4}'
				--AND a.DetailID='132007'
				GROUP BY a.TaskID,a.ProcessingCreator,a.ProcessingDescribe,f.FeedBackDescribe
			) AS a ON a.TaskID=b.TaskID
		) AS b ON USDR.userid=b.ProcessingCreator
	) AS c ON tsp.ShopCode=c.shopcode AND tsp.ShopState<>'已撤店'
	GROUP BY tsp.OperatorAreaName,tsp.ProvinceName,tsp.AreaName,tsp.ShopName,c.ShopCode,tsp.Display,tsp.ShopManager,c.TaskID,c.DetailID,c.FeedBackDescribe,c.ProcessingTime
) AS a 
INNER JOIN t_task_set AS ts ON ts.TaskID=a.TaskID
LEFT JOIN 
(
	SELECT  
		f.DetailID,
		MAX(CASE WHEN f.rn = 1 THEN f.Url END) AS Url1,
		MAX(CASE WHEN f.rn = 2 THEN f.Url END) AS Url2,
		MAX(CASE WHEN f.rn = 3 THEN f.Url END) AS Url3,
		MAX(CASE WHEN f.rn = 4 THEN f.Url END) AS Url4,
		MAX(CASE WHEN f.rn = 5 THEN f.Url END) AS Url5, 
		MAX(CASE WHEN f.rn = 6 THEN f.Url END) AS Url6,
		MAX(CASE WHEN f.rn = 7 THEN f.Url END) AS Url7, 
		MAX(CASE WHEN f.rn = 8 THEN f.Url END) AS Url8, 
		MAX(CASE WHEN f.rn = 9 THEN f.Url END) AS Url9
	FROM 
	( 
		SELECT
			'http://kmer-online-pic.oss-cn-shenzhen.aliyuncs.com/' + ki.Url AS Url,
			tpi.DetailID ,
			ROW_NUMBER() OVER ( PARTITION BY DetailID ORDER BY ki.Url ) AS rn
		FROM dbo.t_task_problem_imglist AS tpi
		LEFT JOIN dbo.kmImages ki ON tpi.ImgID = ki.ID
	) f
	GROUP BY f.DetailID
) AS b ON a.DetailID=b.DetailID
WHERE 1=1
{5}
--AND a.OperatorAreaName='零售1部'
--AND a.ProvinceName='福建'
--AND a.AreaName='福州'
--AND a.Display='陈星宏'
--AND a.ShopManager='林祝年'
", Session["UserName"], txtTimeBegin.ToString("yyyy-MM-dd"),
            txtTimeEnd.ToString("MM-dd"), txtTimeBegin.ToString("yyyy-MM-dd"), txtTimeEnd.AddDays(1).AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss"), strWhere);

        return mydb.DM_GetDateSet(sql);
    }
    

}